package dao.impl;

import dao.StudentDao;
import pojo.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class StudentDaoimpl implements StudentDao {
    @Override
    public List<Student> getAllStu() {
        List<Student> list = new ArrayList<Student>();
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
            String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
            String user = "root";
            String pwd = "1234";
            Connection conn = DriverManager.getConnection(url, user, pwd);
            //3.构造查询命令并执行命令
            String sql = "select * from students";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            list.clear();
            //如果存在数据就一行一行读取
            while (rs.next()) {
                Student student = new Student();
                student.setStuNo(rs.getString(1));
                student.setName(rs.getString(2));
                student.setStuGender(rs.getString(3));
                student.setStuGrade(rs.getString(4));
                student.setStuDepart(rs.getString(5));
                student.setStuMajor(rs.getString(6));
                student.setStuClass(rs.getString(7));
                list.add(student);
            }
            st.close();
            conn.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    @Override
    public Student getStudentByStuNo(String stuNo) {
        Student student = new Student();
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
            String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
            String user = "root";
            String pwd = "1234";
            Connection conn = DriverManager.getConnection(url, user, pwd);

            //3.构造命令并执行命令
            String sql = "select * from students where stuNo = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, stuNo);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                student.setStuNo(rs.getString("stuNo"));
                student.setName(rs.getString("name"));
                student.setStuGender(rs.getString("stuGender"));
                student.setStuGrade(rs.getString("stuGrade"));
                student.setStuDepart(rs.getString("stuDepart"));
                student.setStuMajor(rs.getString("stuMajor"));
                student.setStuClass(rs.getString("stuClass"));
            }
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return student;
    }

    @Override
    public void addStudent(Student student) {
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
            String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
            String user = "root";
            String pwd = "1234";
            Connection conn = DriverManager.getConnection(url, user, pwd);

            //3.构造命令并执行命令
            String sql = "insert into students(stuNo,name,stuGender,stuGrade,stuDepart,stuMajor,stuClass)values(?,?,?,?,?,?,?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, student.getStuNo());
            ps.setString(2, student.getName());
            ps.setString(3, student.getStuGender());
            ps.setString(4, student.getStuGrade());
            ps.setString(5, student.getStuDepart());
            ps.setString(6, student.getStuMajor());
            ps.setString(7, student.getStuClass());
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public void updateStudent(Student student   ) {
        try {
                //1.加载驱动
                Class.forName("com.mysql.cj.jdbc.Driver");

                //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
                String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
                String user = "root";
                String pwd = "1234";
                Connection conn = DriverManager.getConnection(url, user, pwd);

                //3.构造命令并执行命令
                String sql = "update students set name = ?, stuGender = ?, stuGrade = ?, stuDepart = ?, stuMajor = ?, stuClass = ? WHERE stuNo = ?";
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, student.getName());
                ps.setString(2, student.getStuGender());
                ps.setString(3, student.getStuGrade());
                ps.setString(4, student.getStuDepart());
                ps.setString(5, student.getStuMajor());
                ps.setString(6, student.getStuClass());
                ps.setString(7, student.getStuNo());
                ps.executeUpdate();
                ps.close();
                conn.close();

            } catch (Exception e) {
                throw new RuntimeException(e);
            }
    }

    @Override
    public void  deleteStudent(String stuNo) {
        try {
                //1.加载驱动
                Class.forName("com.mysql.cj.jdbc.Driver");

                //2.创建数据库的链接  jdbc:mysql://hostname:port/databasename;
                String url = "jdbc:mysql://localhost:3306/my?serverTimezone=GMT%2B8";
                String user = "root";
                String pwd = "1234";
                Connection conn = DriverManager.getConnection(url, user, pwd);

                //3.构造命令并执行命令
                String sql = "DELETE FROM students WHERE stuNo = '" +stuNo+"'";
                Statement st = conn.createStatement();
                st.executeUpdate(sql);
                st.close();
                conn.close();

            } catch (Exception e) {
                throw new RuntimeException(e);
            }
    }
}
